package com.plug.common.quartz.job;

import com.plug.common.jdbc.DBConnectionManager;
import com.plug.common.jdbc.DBConnectionPool;
import com.plug.common.spring.utils.ApplicationContextHelper;
import com.plug.entity.PlugBackupRecord;
import com.plug.entity.PlugContrast;
import com.plug.entity.PlugDatasource;
import com.plug.entity.PlugTaskState;
import com.plug.service.IPlugBackupRecordService;
import com.plug.service.IPlugContrastService;
import com.plug.service.IPlugDatasourceService;
import com.plug.service.IPlugTaskStateService;
import org.quartz.Job;
import org.quartz.JobExecutionContext;
import org.quartz.JobExecutionException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * Created by slayes on 2017/1/16.
 */
public class QuartzJob implements Job {

    private Logger logger = LoggerFactory.getLogger(getClass());

    private DBConnectionManager dbConnectionManager = DBConnectionManager.getInstance();

    @Override
    public void execute(JobExecutionContext context) throws JobExecutionException {


        logger.info("----------------" + context.getJobDetail().getKey().getName() + "-------------------");
        logger.info("----------任务执行时间------" + new Date() + "-------------------");


        //数据源
        IPlugDatasourceService plugDatasourceService = (IPlugDatasourceService) ApplicationContextHelper.getBean("plugDatasourceService");

        //备份配置
        IPlugContrastService plugContrastService = (IPlugContrastService) ApplicationContextHelper.getBean("plugContrastService");

        //任务运行记录
        IPlugBackupRecordService plugBackupRecordService = (IPlugBackupRecordService) ApplicationContextHelper.getBean("plugBackupRecordService");

        //任务状态记录
        IPlugTaskStateService plugTaskStateService = (IPlugTaskStateService) ApplicationContextHelper.getBean("plugTaskStateService");


        logger.debug("plugContrastService:{},\t\tplugDatasourceService:{}", plugContrastService, plugDatasourceService);
        // List<PlugContrast> recordList = plugContrastService.findAll();//任务对象集合


        List<PlugBackupRecord> plugBackupRecordList = new ArrayList<>(); //任务记录list


        PlugContrast recordPlug = plugContrastService.findTaskID(context.getJobDetail().getKey().getName());

        logger.debug("获取源数据源---------------------{}--------------", recordPlug.getSrcDsId());
        logger.debug("获取目标数据源---------------------{}--------------", recordPlug.getTgtDsId());

        String taskName = "备份任务"+ recordPlug.getName();

        PlugDatasource srcPlugDatasource = plugDatasourceService.findByID(recordPlug.getSrcDsId());//获取源数据源
        PlugDatasource tgtPlugDatasource = plugDatasourceService.findByID(recordPlug.getTgtDsId());//获取目标数据源

        //运行记录
        PlugBackupRecord plugLoggerRecord = new PlugBackupRecord(); //一个空的任务运行记录
        plugLoggerRecord.setSrcDatabase(srcPlugDatasource.getDsName());//任务记录中源数据库名
        plugLoggerRecord.setTgtDatabase(tgtPlugDatasource.getDsName());//任务记录中目标数据库名
        plugLoggerRecord.setSrcTable(recordPlug.getSrcTab());//设置任务记录中的源表
        plugLoggerRecord.setTgtTable(recordPlug.getTgtTab());//设置任务记录中的目标表
        plugLoggerRecord.setPlugContrastId(recordPlug.getId());//设置任务记录中的备份任务配置表ID
        plugLoggerRecord.setActionType(taskName);


        //任务状态日志
        PlugTaskState plugTaskState = plugTaskStateService.selectByName(taskName);
        if(plugTaskState == null) {
            plugTaskState = new PlugTaskState();//新建一条任务状态记录
        }
        plugTaskState.setTaskName(taskName);//获取任务名

        Connection tgtConn = null;
        Connection srcConn = null;

        try {

            logger.debug("srcPlugDatasource.getDsName():{}",srcPlugDatasource.getDsName());
            srcConn = dbConnectionManager.getConnection(DBConnectionPool.createJDBCInfoBean(srcPlugDatasource));
            if(srcConn == null) {
                throw new SQLException("数据库连接超时！");
            }

            logger.debug("tgtPlugDatasource.getDsName():{}",tgtPlugDatasource.getDsName());
            tgtConn = dbConnectionManager.getConnection(DBConnectionPool.createJDBCInfoBean(tgtPlugDatasource));
            if(tgtConn == null) {
                throw new SQLException("数据库连接超时！");
            }


            String findmaxid_sql = "select * from " + recordPlug.getTgtTab() + " order by "+recordPlug.getPrimaryKey()+" desc";
            logger.debug("查询最大ID数:{}", findmaxid_sql);
            PreparedStatement ps = tgtConn.prepareStatement(findmaxid_sql);

            ResultSet rs = ps.executeQuery();
            ResultSetMetaData metaData = rs.getMetaData(); // 取得结果集的元元素
            int colCount = metaData.getColumnCount(); // 取得所有列的个数

            Long maxIndex = 0L;

            StringBuffer columnName = new StringBuffer();
            for ( int j = 1; j <= colCount; j++ ) {
                columnName.append("`" + metaData.getColumnName(j) + "`,");
            }
            columnName.deleteCharAt(columnName.length() - 1);

            while (rs.next()) {
                maxIndex = rs.getLong(recordPlug.getPrimaryKey());
                plugLoggerRecord.setFromNumber(Integer.valueOf(maxIndex.toString()));
                break;
            }

            StringBuffer sqlColumn = new StringBuffer("insert into " + recordPlug.getTgtTab() + " (");
            StringBuffer sqlValue = new StringBuffer();

            for (int j = 1; j <= colCount; j++) {
                sqlColumn.append("`" + metaData.getColumnName(j) + "`,");
                sqlValue.append("?,");
            }

            sqlColumn.deleteCharAt(sqlColumn.length() - 1);
            sqlValue.deleteCharAt(sqlValue.length() - 1);
            sqlColumn.append(") values (" + sqlValue.toString() + ")");

            //设置比较起始行
            int minNumber= 0;

            rs.close();
            ps.close();

            String sql = "";
            String sqlWhere = "";

            if (recordPlug.getSqlWhere() != null && !recordPlug.getSqlWhere().equals(""))
                sqlWhere = " and " + recordPlug.getSqlWhere();

            if(maxIndex == null || maxIndex == 0L){

                ps = srcConn.prepareStatement("select * from " + recordPlug.getSrcTab() + " order by "+recordPlug.getPrimaryKey()+" desc");
                logger.debug("查询最大ID数:{}", "select * from " + recordPlug.getTgtTab() + " order by "+recordPlug.getPrimaryKey()+" desc");
                rs = ps.executeQuery();

                while (rs.next()) {
                    maxIndex = rs.getLong(recordPlug.getPrimaryKey());
                    break;
                }
                rs.close();
                ps.close();

                sql = "select " + columnName.toString() + " from " + recordPlug.getSrcTab() + " where "+recordPlug.getPrimaryKey()+" > " + (maxIndex - 1000L);
                ps = srcConn.prepareStatement(sql + sqlWhere);
                logger.debug("根据最大ID数查询未备份数据:{}", sql + sqlWhere);
                rs = ps.executeQuery();

                tgtConn.setAutoCommit(false);
                PreparedStatement tgtPs = tgtConn.prepareStatement(sqlColumn.toString());
                logger.debug("新增备份数据SQL:{}", sqlColumn.toString());
                while (rs.next()){
                    for (int j = 1; j <= colCount; j++) {
                        Object obj = rs.getObject(j);
                        tgtPs.setObject(j, obj);
                    }
                    minNumber = rs.getInt(recordPlug.getPrimaryKey());
                    tgtPs.addBatch();
                }
                tgtPs.executeBatch();
                tgtConn.commit();
                tgtPs.close();

            } else {

                Long srcMaxIndex = 0L;
                ps = srcConn.prepareStatement("select * from " + recordPlug.getSrcTab() + " order by "+recordPlug.getPrimaryKey()+" desc");
                logger.debug("查询最大ID数:{}", "select * from " + recordPlug.getTgtTab() + " order by "+recordPlug.getPrimaryKey()+" desc");
                rs = ps.executeQuery();

                while (rs.next()) {
                    srcMaxIndex = rs.getLong(recordPlug.getPrimaryKey());
                    break;
                }
                rs.close();
                ps.close();

                Long pages = (srcMaxIndex - maxIndex)/2000;
                Integer page = Integer.valueOf(pages.toString());

                if( page > 0 && ( srcMaxIndex - maxIndex ) % 2000 > 0 )
                    page += 1;

                if(page == 0)
                    page += 1;

                for (int i = 0; i < page; i++){
                    if(page - 1 == i){
                        sql = "select " + columnName.toString() + " from " + recordPlug.getSrcTab() +
                                " where "+recordPlug.getPrimaryKey()+" > " + (i * 2000 + maxIndex) +
                                " and "+recordPlug.getPrimaryKey()+ " <= " + srcMaxIndex;
                    } else {
                        sql = "select " + columnName.toString() + " from " + recordPlug.getSrcTab() +
                                " where "+recordPlug.getPrimaryKey()+" > " + (i * 2000 + maxIndex) +
                                " and "+recordPlug.getPrimaryKey()+ " <= " + ((i + 1) * 2000 + maxIndex);
                    }
                    ps = srcConn.prepareStatement(sql + sqlWhere);
                    logger.debug("根据最大ID数查询未备份数据:{}", sql + sqlWhere);
                    rs = ps.executeQuery();

                    tgtConn.setAutoCommit(false);
                    PreparedStatement tgtPs = tgtConn.prepareStatement(sqlColumn.toString());
                    logger.debug("新增备份数据SQL:{}", sqlColumn.toString());
                    while (rs.next()){
                        for (int j = 1; j <= colCount; j++) {
                            Object obj = rs.getObject(j);
                            tgtPs.setObject(j, obj);
                        }
                        minNumber = rs.getInt(recordPlug.getPrimaryKey());
                        tgtPs.addBatch();
                    }
                    tgtPs.executeBatch();
                    tgtConn.commit();
                    tgtPs.close();
                }
            }

            logger.debug("备份结束");

            //设置备份状态、时间和结果提示信息
            //设置比较终止行
            plugLoggerRecord.setToNumber(minNumber);
            plugLoggerRecord.setCreateTime(new Date());
            plugLoggerRecord.setState(0);
            plugLoggerRecord.setMessage("");

            if(plugLoggerRecord.getToNumber() != 0) {
                plugBackupRecordList.add(plugLoggerRecord);
            }
//            任务运行成功，无异常
            plugTaskState.setMessage("");
            plugTaskState.setTaskState("运行中");

        }catch (Exception e) {

            logger.error("备份任务异常："+e);
            //设置备份状态和结果提示信息
            plugLoggerRecord.setToNumber(0);
            plugLoggerRecord.setFromNumber(0);
            plugLoggerRecord.setState(1);
            plugLoggerRecord.setCreateTime(new Date());
            plugLoggerRecord.setMessage(e.getMessage());

            plugBackupRecordList.add(plugLoggerRecord);
//            任务运行异常
            plugTaskState.setMessage(e.getMessage());
            plugTaskState.setTaskState("未完成，有异常");

        }finally {

            if(srcConn != null){
                dbConnectionManager.freeConnection(srcPlugDatasource.getDsName(), srcConn);
            }

            if(tgtConn != null){
                dbConnectionManager.freeConnection(tgtPlugDatasource.getDsName(), tgtConn);
            }

            if(plugLoggerRecord.getFromNumber() == null)
                plugLoggerRecord.setFromNumber(0);
            if(plugLoggerRecord.getState() == null)
                plugLoggerRecord.setState(1);
            //任务最后修改时间
            plugTaskState.setEndTime(new Date());

        }

        if(plugTaskStateService.selectByName(taskName)==null){
            plugTaskStateService.addTaskState(plugTaskState);//新增记录
        }else{
            plugTaskStateService.updateTime(plugTaskState);//修改记录
        }

        if(plugBackupRecordList != null && plugBackupRecordList.size() > 0) {
            plugBackupRecordService.saveBatch(plugBackupRecordList);
        }
    }
}
